/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.sjtu.bean;

import com.sjtu.database.DataBase;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Set;

/**
 *
 * @author p w
 */
public class CategoryBean {

    private int id;
    private String content;
    private int parentId;

    public CategoryBean(int id, String content, int parentId) {
        this.id = id;
        this.content = content;
        this.parentId = parentId;
    }

    public String getContent() {
        return content;
    }

    public void setContent(String content) {
        this.content = content;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public int getParentId() {
        return parentId;
    }

    public void setParentId(int parentId) {
        this.parentId = parentId;
    }

    public static CategoryBean getCategory(int categoryId) {
        try {
            DataBase database = DataBase.getInstance();
            ResultSet rs = database.query("select * from category where id = '" + categoryId + "';");
            CategoryBean category = setCategory(rs);
            rs.close();
            return category;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public static CategoryBean getCategory(String categoryName) {
        try {
            DataBase database = DataBase.getInstance();
            ResultSet rs = database.query("select * from category where content = '" + categoryName + "';");
            CategoryBean category = setCategory(rs);
            rs.close();
            return category;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    private static CategoryBean setCategory(ResultSet rs) {
        try {
            if (rs == null || !rs.next()) {
                return null;
            }
            return new CategoryBean(rs.getInt("id"), rs.getString("content"), rs.getInt("parentId"));
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public static ArrayList<String> mainCategory() {
        ArrayList<String> mainCategory = new ArrayList<String>();
        try {
            DataBase database = DataBase.getInstance();
            ResultSet rs;
            rs = database.query("select * from category where parentId = 1 and content != 'Category';");
            while (rs.next()) {
//                System.out.println(rs.getString("content"));
                mainCategory.add(rs.getString("content"));
            }
            return mainCategory;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static CategoryBean addCategory(String content, String parentContent) {
        try {
            DataBase database = DataBase.getInstance();
            String sql;
            ResultSet rs = database.query("select * from category where content = '" + content + "';");
            if (rs.next()) {
                return new CategoryBean(rs.getInt("id"), content, rs.getInt("parentId"));
            }
            if (parentContent.equals("Category")) {
                sql = "insert into category (content,parentId) values ('" + content + "'," + 1 + ");";
            } else {
                int parentId;
                rs = database.query("select id from category where content = '" + parentContent + "';");
                if (rs.next()) {
                    parentId = rs.getInt("id");
                } else {
//                    System.out.println("no such parent category");
                    return null;
                }
                sql = "insert into category (content,parentId) values ('" + content + "'," + parentId + ");";
            }
//            System.out.println(sql);
            database.update(sql);
            rs = database.query("select * from category where content = '" + content + "';");
            if (rs.next()) {
                return new CategoryBean(rs.getInt("id"), content, rs.getInt("parentId"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static ArrayList<String> subcategory(String content) {
        ArrayList<String> subCates = new ArrayList<String>();
        try {
            int parentid = 0;
            DataBase database = DataBase.getInstance();
            ResultSet rs = database.query("select id from category where content = '" + content + "';");
            if (rs.next()) {
                parentid = rs.getInt("id");
            } else {
                return null;
            }
            rs = database.query("select content from category where parentid = " + parentid + " and content != '';");
            while (rs.next()) {
                subCates.add(rs.getString("content"));
            }
            return subCates;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static ArrayList<String> categoryUsers(int categoryId) {
        ArrayList<Integer> categoryUserIdList = new ArrayList<Integer>();
        ArrayList<String> categoryUserList = new ArrayList<String>();
//        String userMail = "";
        try {
            DataBase database = DataBase.getInstance();
            System.out.println("select userId from usercategoryinfo where categoryId = " + categoryId + ";");
            ResultSet rs = database.query("select userId from usercategoryinfo where categoryId = " + categoryId + ";");
            while (rs.next()) {
                int userId = rs.getInt("userId");
                categoryUserIdList.add(userId);
            }
            for (int i = 0; i < categoryUserIdList.size(); i++) {
                ResultSet result = database.query("select email from user where id = " + categoryUserIdList.get(i) + ";");
                result.next();
                String mail = result.getString("email");
                if (!categoryUserList.contains(mail)) {
                    categoryUserList.add(mail);
                }
            }
            return categoryUserList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}
